'\" t
.\"     Title: CREATE AGGREGATE
.\"    Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets v1.75.2 <http://docbook.sf.net/>
.\"      Date: 2011-12-01
.\"    Manual: PostgreSQL 9.1.2 Documentation
.\"    Source: PostgreSQL 9.1.2
.\"  Language: English
.\"
.TH "CREATE AGGREGATE" "7" "2011-12-01" "PostgreSQL 9.1.2" "PostgreSQL 9.1.2 Documentation"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
CREATE_AGGREGATE \- define a new aggregate function
.\" CREATE AGGREGATE
.SH "SYNOPSIS"
.sp
.nf
CREATE AGGREGATE \fIname\fR ( \fIinput_data_type\fR [ , \&.\&.\&. ] ) (
    SFUNC = \fIsfunc\fR,
    STYPE = \fIstate_data_type\fR
    [ , FINALFUNC = \fIffunc\fR ]
    [ , INITCOND = \fIinitial_condition\fR ]
    [ , SORTOP = \fIsort_operator\fR ]
)

or the old syntax

CREATE AGGREGATE \fIname\fR (
    BASETYPE = \fIbase_type\fR,
    SFUNC = \fIsfunc\fR,
    STYPE = \fIstate_data_type\fR
    [ , FINALFUNC = \fIffunc\fR ]
    [ , INITCOND = \fIinitial_condition\fR ]
    [ , SORTOP = \fIsort_operator\fR ]
)
.fi
.SH "DESCRIPTION"
.PP

CREATE AGGREGATE
defines a new aggregate function\&. Some basic and commonly\-used aggregate functions are included with the distribution; they are documented in
Section 9.18, \(lqAggregate Functions\(rq, in the documentation\&. If one defines new types or needs an aggregate function not already provided, then
CREATE AGGREGATE
can be used to provide the desired features\&.
.PP
If a schema name is given (for example,
CREATE AGGREGATE myschema\&.myagg \&.\&.\&.) then the aggregate function is created in the specified schema\&. Otherwise it is created in the current schema\&.
.PP
An aggregate function is identified by its name and input data type(s)\&. Two aggregates in the same schema can have the same name if they operate on different input types\&. The name and input data type(s) of an aggregate must also be distinct from the name and input data type(s) of every ordinary function in the same schema\&.
.PP
An aggregate function is made from one or two ordinary functions: a state transition function
\fIsfunc\fR, and an optional final calculation function
\fIffunc\fR\&. These are used as follows:
.sp
.if n \{\
.RS 4
.\}
.nf
\fIsfunc\fR( internal\-state, next\-data\-values ) \-\-\-> next\-internal\-state
\fIffunc\fR( internal\-state ) \-\-\-> aggregate\-value
.fi
.if n \{\
.RE
.\}
.PP

PostgreSQL
creates a temporary variable of data type
\fIstype\fR
to hold the current internal state of the aggregate\&. At each input row, the aggregate argument value(s) are calculated and the state transition function is invoked with the current state value and the new argument value(s) to calculate a new internal state value\&. After all the rows have been processed, the final function is invoked once to calculate the aggregate\*(Aqs return value\&. If there is no final function then the ending state value is returned as\-is\&.
.PP
An aggregate function can provide an initial condition, that is, an initial value for the internal state value\&. This is specified and stored in the database as a value of type
text, but it must be a valid external representation of a constant of the state value data type\&. If it is not supplied then the state value starts out null\&.
.PP
If the state transition function is declared
\(lqstrict\(rq, then it cannot be called with null inputs\&. With such a transition function, aggregate execution behaves as follows\&. Rows with any null input values are ignored (the function is not called and the previous state value is retained)\&. If the initial state value is null, then at the first row with all\-nonnull input values, the first argument value replaces the state value, and the transition function is invoked at subsequent rows with all\-nonnull input values\&. This is handy for implementing aggregates like
\fBmax\fR\&. Note that this behavior is only available when
\fIstate_data_type\fR
is the same as the first
\fIinput_data_type\fR\&. When these types are different, you must supply a nonnull initial condition or use a nonstrict transition function\&.
.PP
If the state transition function is not strict, then it will be called unconditionally at each input row, and must deal with null inputs and null transition values for itself\&. This allows the aggregate author to have full control over the aggregate\*(Aqs handling of null values\&.
.PP
If the final function is declared
\(lqstrict\(rq, then it will not be called when the ending state value is null; instead a null result will be returned automatically\&. (Of course this is just the normal behavior of strict functions\&.) In any case the final function has the option of returning a null value\&. For example, the final function for
\fBavg\fR
returns null when it sees there were zero input rows\&.
.PP
Aggregates that behave like
\fBMIN\fR
or
\fBMAX\fR
can sometimes be optimized by looking into an index instead of scanning every input row\&. If this aggregate can be so optimized, indicate it by specifying a
sort operator\&. The basic requirement is that the aggregate must yield the first element in the sort ordering induced by the operator; in other words:
.sp
.if n \{\
.RS 4
.\}
.nf
SELECT agg(col) FROM tab;
.fi
.if n \{\
.RE
.\}
.sp
must be equivalent to:
.sp
.if n \{\
.RS 4
.\}
.nf
SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
.fi
.if n \{\
.RE
.\}
.sp
Further assumptions are that the aggregate ignores null inputs, and that it delivers a null result if and only if there were no non\-null inputs\&. Ordinarily, a data type\*(Aqs
<
operator is the proper sort operator for
\fBMIN\fR, and
>
is the proper sort operator for
\fBMAX\fR\&. Note that the optimization will never actually take effect unless the specified operator is the
\(lqless than\(rq
or
\(lqgreater than\(rq
strategy member of a B\-tree index operator class\&.
.SH "PARAMETERS"
.PP
\fIname\fR
.RS 4
The name (optionally schema\-qualified) of the aggregate function to create\&.
.RE
.PP
\fIinput_data_type\fR
.RS 4
An input data type on which this aggregate function operates\&. To create a zero\-argument aggregate function, write
*
in place of the list of input data types\&. (An example of such an aggregate is
\fBcount(*)\fR\&.)
.RE
.PP
\fIbase_type\fR
.RS 4
In the old syntax for
CREATE AGGREGATE, the input data type is specified by a
basetype
parameter rather than being written next to the aggregate name\&. Note that this syntax allows only one input parameter\&. To define a zero\-argument aggregate function, specify the
basetype
as
"ANY"
(not
*)\&.
.RE
.PP
\fIsfunc\fR
.RS 4
The name of the state transition function to be called for each input row\&. For an
\fIN\fR\-argument aggregate function, the
\fIsfunc\fR
must take
\fIN\fR+1 arguments, the first being of type
\fIstate_data_type\fR
and the rest matching the declared input data type(s) of the aggregate\&. The function must return a value of type
\fIstate_data_type\fR\&. This function takes the current state value and the current input data value(s), and returns the next state value\&.
.RE
.PP
\fIstate_data_type\fR
.RS 4
The data type for the aggregate\*(Aqs state value\&.
.RE
.PP
\fIffunc\fR
.RS 4
The name of the final function called to compute the aggregate\*(Aqs result after all input rows have been traversed\&. The function must take a single argument of type
\fIstate_data_type\fR\&. The return data type of the aggregate is defined as the return type of this function\&. If
\fIffunc\fR
is not specified, then the ending state value is used as the aggregate\*(Aqs result, and the return type is
\fIstate_data_type\fR\&.
.RE
.PP
\fIinitial_condition\fR
.RS 4
The initial setting for the state value\&. This must be a string constant in the form accepted for the data type
\fIstate_data_type\fR\&. If not specified, the state value starts out null\&.
.RE
.PP
\fIsort_operator\fR
.RS 4
The associated sort operator for a
\fBMIN\fR\- or
\fBMAX\fR\-like aggregate\&. This is just an operator name (possibly schema\-qualified)\&. The operator is assumed to have the same input data types as the aggregate (which must be a single\-argument aggregate)\&.
.RE
.PP
The parameters of
CREATE AGGREGATE
can be written in any order, not just the order illustrated above\&.
.SH "EXAMPLES"
.PP
See
Section 35.10, \(lqUser-defined Aggregates\(rq, in the documentation\&.
.SH "COMPATIBILITY"
.PP

CREATE AGGREGATE
is a
PostgreSQL
language extension\&. The SQL standard does not provide for user\-defined aggregate functions\&.
.SH "SEE ALSO"
ALTER AGGREGATE (\fBALTER_AGGREGATE\fR(7)), DROP AGGREGATE (\fBDROP_AGGREGATE\fR(7))
